﻿using System;
using System.Xml;
using System.Configuration;
using UwaySoft.EsheetServer.BizLogic;
using UwaySoft.EsheetServer.DataLayer;
using UwaySoft.EsheetServer.Help.Response;
using UwaySoft.EsheetServer.Help.Template;

namespace UwaySoft.EsheetServer.Help.WorkDAL
{
    public class NewSheetDAL : IWorkDAL
    {
        private static UserCityHelper _userCityHelper;

        protected UserCityHelper UserCityHelper
        {
            get { return _userCityHelper ?? (_userCityHelper = new UserCityHelper()); }
        }

        protected ISqlHelper SqlHelper
        {
            get { return Application.SqlHelper; }
        }

        protected IResponse AddWorkOrder(NewSheetModel sheetModel)
        {
            var workOrderId = Guid.NewGuid();

            bool isRepeat = false;/* SqlHelper.ExecuteScalar<int>(
                                        "SELECT COUNT(*) FROM MOD_COMPLAINTS_ITEM WHERE SERIALNO=:SERIALNO",
                                        SqlHelper.CreateParameter(":SERIALNO", sheetModel.Serialno)) > 0;*/

            //用户投诉归属地
            var cityid = UserCityHelper.GetCityId(sheetModel.Usercomplaintcity);

            SqlHelper.ExecuteNonQuery(@"INSERT INTO MOD_COMPLAINTS_ITEM
                                              (SHEETID,
                                               EXPLAIN,
                                               USERTYPE,
                                               USERMEAL,
                                               USERSIGN,
                                               EMAIL,
                                               USERREMARK,
                                               USERCITY,
                                               CALLNO,
                                               ACCEPTPHONE,
                                               USERPHONE1,
                                               USERPHONE2,
                                               OWNER,
                                               CONTACTNAME,
                                               MANAGER,
                                               USERMANAGERTEL,
                                               NETTIME,
                                               GREENSIGN,
                                               FAXNO,
                                               GROUPSERIAL,
                                               GROUPMAINNAME,
                                               GROUPMAINTEL,
                                               SELLCENTER,
                                               MAINSERVICEATTIUDE,
                                               SERIALNO,
                                               ACCEPTID,
                                               ACCEPTTIME,
                                               SERVICECITY,
                                               SHEETAREA,
                                               COMPLAINROUTE,
                                               CONTATCID,
                                               LANGUAGE,
                                               HOPEREPLYTIME,
                                               TROUBLEAPPEARANCE,
                                               TERMINATYPE,
                                               BLACKLISTFLAG,
                                               ACCEPTERNAME,
                                               ACCEPTERTEL,
                                               DUFFTYPE,
                                               ISUPGRADEAPPEAL,
                                               CALLINCOUNT,
                                               ISREPLY,
                                               ISFACUSAPPEAL,
                                               SPENTERNUM,
                                               SPSERVICENAME,
                                               SPFACTORY,
                                               SPCORPNO,
                                               DUTYASCERTAIN,
                                               TYPEATTRIBUTE,
                                               SPNO,
                                               SPNAME,
                                               SPPEACEBREAKER,
                                               SPCONNECTMODE,
                                               SPFORCECUSTOMIZE,
                                               SPSTARTDATE,
                                               SPENDDATE,
                                               SPISALL,
                                               WITHDRAWFEE,
                                               REALWITHDRAWFEE,
                                               COMPENSATETIMES,
                                               COMPENSATETTAL,
                                               SERVICECLASSID,
                                               SERVICECONTENT,
                                               SUMPOWERNUM,
                                               AFFID,
                                               ADDRESS,
                                               STREETINFO,
                                               BUILDINGINFO,
                                               TEMPLATEID,
                                               SERVICENAME,
                                               PAYTYPE,
                                               PAYMETHOD,
                                               PAYMONEY,
                                               PAYCLASS,
                                               PAYREASON,
                                               PAYACCEPTNO,
                                               ISPAY,
                                               ISACCEPT,
                                               DUTYORGA,
                                               OPERATIONPRODUCT,
                                               OPTIONADDBYH1,
                                               MUSTHANDLEDATE,
                                               COMPLAINTS_ID,
                                               CITY_ID,
                                               SERIALINFONO,
                                               ISREPEAT,
                                               USERCOMPLAINTCITY,
                                               CUSTOMERGRADE,
                                               ISATTACHMENT)
                                            VALUES
                                              (:SHEETID,
                                               :EXPLAIN,
                                               :USERTYPE,
                                               :USERMEAL,
                                               :USERSIGN,
                                               :EMAIL,
                                               :USERREMARK,
                                               :USERCITY,
                                               :CALLNO,
                                               :ACCEPTPHONE,
                                               :USERPHONE1,
                                               :USERPHONE2,
                                               :OWNER,
                                               :CONTACTNAME,
                                               :MANAGER,
                                               :USERMANAGERTEL,
                                               :NETTIME,
                                               :GREENSIGN,
                                               :FAXNO,
                                               :GROUPSERIAL,
                                               :GROUPMAINNAME,
                                               :GROUPMAINTEL,
                                               :SELLCENTER,
                                               :MAINSERVICEATTIUDE,
                                               :SERIALNO,
                                               :ACCEPTID,
                                               :ACCEPTTIME,
                                               :SERVICECITY,
                                               :SHEETAREA,
                                               :COMPLAINROUTE,
                                               :CONTATCID,
                                               :LANGUAGE,
                                               :HOPEREPLYTIME,
                                               :TROUBLEAPPEARANCE,
                                               :TERMINATYPE,
                                               :BLACKLISTFLAG,
                                               :ACCEPTERNAME,
                                               :ACCEPTERTEL,
                                               :DUFFTYPE,
                                               :ISUPGRADEAPPEAL,
                                               :CALLINCOUNT,
                                               :ISREPLY,
                                               :ISFACUSAPPEAL,
                                               :SPENTERNUM,
                                               :SPSERVICENAME,
                                               :SPFACTORY,
                                               :SPCORPNO,
                                               :DUTYASCERTAIN,
                                               :TYPEATTRIBUTE,
                                               :SPNO,
                                               :SPNAME,
                                               :SPPEACEBREAKER,
                                               :SPCONNECTMODE,
                                               :SPFORCECUSTOMIZE,
                                               :SPSTARTDATE,
                                               :SPENDDATE,
                                               :SPISALL,
                                               :WITHDRAWFEE,
                                               :REALWITHDRAWFEE,
                                               :COMPENSATETIMES,
                                               :COMPENSATETTAL,
                                               :SERVICECLASSID,
                                               :SERVICECONTENT,
                                               :SUMPOWERNUM,
                                               :AFFID,
                                               :ADDRESS,
                                               :STREETINFO,
                                               :BUILDINGINFO,
                                               :TEMPLATEID,
                                               :SERVICENAME,
                                               :PAYTYPE,
                                               :PAYMETHOD,
                                               :PAYMONEY,
                                               :PAYCLASS,
                                               :PAYREASON,
                                               :PAYACCEPTNO,
                                               :ISPAY,
                                               :ISACCEPT,
                                               :DUTYORGA,
                                               :OPERATIONPRODUCT,
                                               :OPTIONADDBYH1,
                                               :MUSTHANDLEDATE,
                                               :COMPLAINTS_ID,
                                               :CITY_ID,
                                               :SERIALINFONO,
                                               :ISREPEAT,
                                               :USERCOMPLAINTCITY,
                                               :CUSTOMERGRADE,
                                               :ISATTACHMENT)",
                                            SqlHelper.CreateParameter(":SHEETID", workOrderId.ToString()),
                                            SqlHelper.CreateParameter(":EXPLAIN", CheckedDbValue(sheetModel.Explain)),
                                            SqlHelper.CreateParameter(":USERTYPE", CheckedDbValue(sheetModel.Usertype)),
                                            SqlHelper.CreateParameter(":USERMEAL", CheckedDbValue(sheetModel.Usermeal)),
                                            SqlHelper.CreateParameter(":USERSIGN", CheckedDbValue(sheetModel.Usersign)),
                                            SqlHelper.CreateParameter(":EMAIL", CheckedDbValue(sheetModel.Email)),
                                            SqlHelper.CreateParameter(":USERREMARK", CheckedDbValue(sheetModel.Userremark)),
                                            SqlHelper.CreateParameter(":USERCITY", CheckedDbValue(sheetModel.Usercity)),
                                            SqlHelper.CreateParameter(":CALLNO", CheckedDbValue(sheetModel.Callno)),
                                            SqlHelper.CreateParameter(":ACCEPTPHONE", CheckedDbValue(sheetModel.Acceptphone)),
                                            SqlHelper.CreateParameter(":USERPHONE1", CheckedDbValue(sheetModel.Userphone1)),
                                            SqlHelper.CreateParameter(":USERPHONE2", CheckedDbValue(sheetModel.Userphone2)),
                                            SqlHelper.CreateParameter(":OWNER", CheckedDbValue(sheetModel.Owner)),
                                            SqlHelper.CreateParameter(":CONTACTNAME", CheckedDbValue(sheetModel.Contactname)),
                                            SqlHelper.CreateParameter(":MANAGER", CheckedDbValue(sheetModel.Manager)),
                                            SqlHelper.CreateParameter(":USERMANAGERTEL", CheckedDbValue(sheetModel.Usermanagertel)),
                                            SqlHelper.CreateParameter(":NETTIME", CheckedDbValue(sheetModel.Nettime)),
                                            SqlHelper.CreateParameter(":GREENSIGN", CheckedDbValue(sheetModel.Greensign)),
                                            SqlHelper.CreateParameter(":FAXNO", CheckedDbValue(sheetModel.Faxno)),
                                            SqlHelper.CreateParameter(":GROUPSERIAL", CheckedDbValue(sheetModel.Groupserial)),
                                            SqlHelper.CreateParameter(":GROUPMAINNAME", CheckedDbValue(sheetModel.Groupmainname)),
                                            SqlHelper.CreateParameter(":GROUPMAINTEL", CheckedDbValue(sheetModel.Groupmaintel)),
                                            SqlHelper.CreateParameter(":SELLCENTER", CheckedDbValue(sheetModel.Sellcenter)),
                                            SqlHelper.CreateParameter(":MAINSERVICEATTIUDE", CheckedDbValue(sheetModel.Mainserviceattiude)),
                                            SqlHelper.CreateParameter(":SERIALNO", CheckedDbValue(sheetModel.Serialno)),
                                            SqlHelper.CreateParameter(":ACCEPTID", CheckedDbValue(sheetModel.Acceptid)),
                                            SqlHelper.CreateParameter(":ACCEPTTIME", CheckedDbValue(sheetModel.Accepttime)),
                                            SqlHelper.CreateParameter(":SERVICECITY", CheckedDbValue(sheetModel.Servicecity)),
                                            SqlHelper.CreateParameter(":SHEETAREA", CheckedDbValue(sheetModel.Sheetarea)),
                                            SqlHelper.CreateParameter(":COMPLAINROUTE", CheckedDbValue(sheetModel.Complainroute)),
                                            SqlHelper.CreateParameter(":CONTATCID", CheckedDbValue(sheetModel.Contatcid)),
                                            SqlHelper.CreateParameter(":LANGUAGE", CheckedDbValue(sheetModel.Language)),
                                            SqlHelper.CreateParameter(":HOPEREPLYTIME", CheckedDbValue(sheetModel.Hopereplytime)),
                                            SqlHelper.CreateParameter(":TROUBLEAPPEARANCE", CheckedDbValue(sheetModel.Troubleappearance)),
                                            SqlHelper.CreateParameter(":TERMINATYPE", CheckedDbValue(sheetModel.Terminatype)),
                                            SqlHelper.CreateParameter(":BLACKLISTFLAG", CheckedDbValue(sheetModel.Blacklistflag)),
                                            SqlHelper.CreateParameter(":ACCEPTERNAME", CheckedDbValue(sheetModel.Acceptername)),
                                            SqlHelper.CreateParameter(":ACCEPTERTEL", CheckedDbValue(sheetModel.Acceptertel)),
                                            SqlHelper.CreateParameter(":DUFFTYPE", CheckedDbValue(sheetModel.Dufftype)),
                                            SqlHelper.CreateParameter(":ISUPGRADEAPPEAL", CheckedDbValue(sheetModel.Isupgradeappeal)),
                                            SqlHelper.CreateParameter(":CALLINCOUNT", CheckedDbValue(sheetModel.Callincount)),
                                            SqlHelper.CreateParameter(":ISREPLY", CheckedDbValue(sheetModel.Isreply)),
                                            SqlHelper.CreateParameter(":ISFACUSAPPEAL", CheckedDbValue(sheetModel.Isfacusappeal)),
                                            SqlHelper.CreateParameter(":SPENTERNUM", CheckedDbValue(sheetModel.Spenternum)),
                                            SqlHelper.CreateParameter(":SPSERVICENAME", CheckedDbValue(sheetModel.Spservicename)),
                                            SqlHelper.CreateParameter(":SPFACTORY", CheckedDbValue(sheetModel.Spfactory)),
                                            SqlHelper.CreateParameter(":SPCORPNO", CheckedDbValue(sheetModel.Spcorpno)),
                                            SqlHelper.CreateParameter(":DUTYASCERTAIN", CheckedDbValue(sheetModel.Dutyascertain)),
                                            SqlHelper.CreateParameter(":TYPEATTRIBUTE", CheckedDbValue(sheetModel.Typeattribute)),
                                            SqlHelper.CreateParameter(":SPNO", CheckedDbValue(sheetModel.Spno)),
                                            SqlHelper.CreateParameter(":SPNAME", CheckedDbValue(sheetModel.Spname)),
                                            SqlHelper.CreateParameter(":SPPEACEBREAKER", CheckedDbValue(sheetModel.Sppeacebreaker)),
                                            SqlHelper.CreateParameter(":SPCONNECTMODE", CheckedDbValue(sheetModel.Spconnectmode)),
                                            SqlHelper.CreateParameter(":SPFORCECUSTOMIZE", CheckedDbValue(sheetModel.Spforcecustomize)),
                                            SqlHelper.CreateParameter(":SPSTARTDATE", CheckedDbValue(sheetModel.Spstartdate)),
                                            SqlHelper.CreateParameter(":SPENDDATE", CheckedDbValue(sheetModel.Spenddate)),
                                            SqlHelper.CreateParameter(":SPISALL", CheckedDbValue(sheetModel.Spisall)),
                                            SqlHelper.CreateParameter(":WITHDRAWFEE", CheckedDbValue(sheetModel.Withdrawfee)),
                                            SqlHelper.CreateParameter(":REALWITHDRAWFEE", CheckedDbValue(sheetModel.Realwithdrawfee)),
                                            SqlHelper.CreateParameter(":COMPENSATETIMES", CheckedDbValue(sheetModel.Compensatetimes)),
                                            SqlHelper.CreateParameter(":COMPENSATETTAL", CheckedDbValue(sheetModel.Compensatettal)),
                                            SqlHelper.CreateParameter(":SERVICECLASSID", CheckedDbValue(sheetModel.Serviceclassid)),
                                            SqlHelper.CreateParameter(":SERVICECONTENT", CheckedDbValue(sheetModel.Servicecontent)),
                                            SqlHelper.CreateParameter(":SUMPOWERNUM", CheckedDbValue(sheetModel.Sumpowernum)),
                                            SqlHelper.CreateParameter(":AFFID", CheckedDbValue(sheetModel.Affid)),
                                            SqlHelper.CreateParameter(":ADDRESS", CheckedDbValue(sheetModel.Address)),
                                            SqlHelper.CreateParameter(":STREETINFO", CheckedDbValue(sheetModel.Streetinfo)),
                                            SqlHelper.CreateParameter(":BUILDINGINFO", CheckedDbValue(sheetModel.Buildinginfo)),
                                            SqlHelper.CreateParameter(":TEMPLATEID", CheckedDbValue(sheetModel.Templateid)),
                                            SqlHelper.CreateParameter(":SERVICENAME", CheckedDbValue(sheetModel.Servicename)),
                                            SqlHelper.CreateParameter(":PAYTYPE", CheckedDbValue(sheetModel.Paytype)),
                                            SqlHelper.CreateParameter(":PAYMETHOD", CheckedDbValue(sheetModel.Paymethod)),
                                            SqlHelper.CreateParameter(":PAYMONEY", CheckedDbValue(sheetModel.Paymoney)),
                                            SqlHelper.CreateParameter(":PAYCLASS", CheckedDbValue(sheetModel.Payclass)),
                                            SqlHelper.CreateParameter(":PAYREASON", CheckedDbValue(sheetModel.Payreason)),
                                            SqlHelper.CreateParameter(":PAYACCEPTNO", CheckedDbValue(sheetModel.Payacceptno)),
                                            SqlHelper.CreateParameter(":ISPAY", CheckedDbValue(sheetModel.Ispay)),
                                            SqlHelper.CreateParameter(":ISACCEPT", CheckedDbValue(sheetModel.Isaccept)),
                                            SqlHelper.CreateParameter(":DUTYORGA", CheckedDbValue(sheetModel.Dutyorga)),
                                            SqlHelper.CreateParameter(":OPERATIONPRODUCT", CheckedDbValue(sheetModel.Operationproduct)),
                                            SqlHelper.CreateParameter(":OPTIONADDBYH1", CheckedDbValue(sheetModel.Optionaddbyh1)),
                                            SqlHelper.CreateParameter(":MUSTHANDLEDATE", CheckedDbValue(sheetModel.Musthandledate)),
                                            SqlHelper.CreateParameter(":COMPLAINTS_ID", workOrderId.ToString()),
                                            SqlHelper.CreateParameter(":CITY_ID", cityid == -1 ? UserCityHelper.GetCityId(sheetModel.Usercity) : cityid/*UserCityHelper.GetCityId(workOrderDemo.Usercity)*/),
                                            SqlHelper.CreateParameter(":SERIALINFONO", CheckedDbValue(sheetModel.Serialinfono)),
                                            SqlHelper.CreateParameter(":ISREPEAT", (int)Convert.ToByte(isRepeat)),
                                            SqlHelper.CreateParameter(":USERCOMPLAINTCITY", CheckedDbValue(sheetModel.Usercomplaintcity)),
                                            SqlHelper.CreateParameter(":CUSTOMERGRADE", CheckedDbValue(sheetModel.Customergrade)),
                                            SqlHelper.CreateParameter(":ISATTACHMENT", CheckedDbValue(sheetModel.Isattachment)));

            return new NewSheetResponse(true, sheetModel.Serialno, workOrderId.ToString(), sheetModel.Serialinfono, @"工单 接收成功");
        }

        protected IResponse AddCheckOrder(NewSheetModel sheetModel)
        {
            var workOrderId = Guid.NewGuid();

            bool isRepeat = SqlHelper.ExecuteScalar<int>(
                                        "SELECT COUNT(*) FROM MOD_COMPLAINTS_ITEM WHERE SERIALNO=:SERIALNO",
                                        SqlHelper.CreateParameter(":SERIALNO", sheetModel.Serialno)) > 0;

            //用户投诉归属地
            var cityid = UserCityHelper.GetCityId(sheetModel.Usercomplaintcity);

            SqlHelper.ExecuteNonQuery(@"INSERT INTO MOD_COMPLAINTS_ITEM_CHECK
                                              (SHEETID,
                                               EXPLAIN,
                                               USERTYPE,
                                               USERMEAL,
                                               USERSIGN,
                                               EMAIL,
                                               USERREMARK,
                                               USERCITY,
                                               CALLNO,
                                               ACCEPTPHONE,
                                               USERPHONE1,
                                               USERPHONE2,
                                               OWNER,
                                               CONTACTNAME,
                                               MANAGER,
                                               USERMANAGERTEL,
                                               NETTIME,
                                               GREENSIGN,
                                               FAXNO,
                                               GROUPSERIAL,
                                               GROUPMAINNAME,
                                               GROUPMAINTEL,
                                               SELLCENTER,
                                               MAINSERVICEATTIUDE,
                                               SERIALNO,
                                               ACCEPTID,
                                               ACCEPTTIME,
                                               SERVICECITY,
                                               SHEETAREA,
                                               COMPLAINROUTE,
                                               CONTATCID,
                                               LANGUAGE,
                                               HOPEREPLYTIME,
                                               TROUBLEAPPEARANCE,
                                               TERMINATYPE,
                                               BLACKLISTFLAG,
                                               ACCEPTERNAME,
                                               ACCEPTERTEL,
                                               DUFFTYPE,
                                               ISUPGRADEAPPEAL,
                                               CALLINCOUNT,
                                               ISREPLY,
                                               ISFACUSAPPEAL,
                                               SPENTERNUM,
                                               SPSERVICENAME,
                                               SPFACTORY,
                                               SPCORPNO,
                                               DUTYASCERTAIN,
                                               TYPEATTRIBUTE,
                                               SPNO,
                                               SPNAME,
                                               SPPEACEBREAKER,
                                               SPCONNECTMODE,
                                               SPFORCECUSTOMIZE,
                                               SPSTARTDATE,
                                               SPENDDATE,
                                               SPISALL,
                                               WITHDRAWFEE,
                                               REALWITHDRAWFEE,
                                               COMPENSATETIMES,
                                               COMPENSATETTAL,
                                               SERVICECLASSID,
                                               SERVICECONTENT,
                                               SUMPOWERNUM,
                                               AFFID,
                                               ADDRESS,
                                               STREETINFO,
                                               BUILDINGINFO,
                                               TEMPLATEID,
                                               SERVICENAME,
                                               PAYTYPE,
                                               PAYMETHOD,
                                               PAYMONEY,
                                               PAYCLASS,
                                               PAYREASON,
                                               PAYACCEPTNO,
                                               ISPAY,
                                               ISACCEPT,
                                               DUTYORGA,
                                               OPERATIONPRODUCT,
                                               OPTIONADDBYH1,
                                               MUSTHANDLEDATE,
                                               COMPLAINTS_ID,
                                               CITY_ID,
                                               SERIALINFONO,
                                               ISREPEAT,
                                               USERCOMPLAINTCITY,
                                               CUSTOMERGRADE,
                                               ISATTACHMENT)
                                            VALUES
                                              (:SHEETID,
                                               :EXPLAIN,
                                               :USERTYPE,
                                               :USERMEAL,
                                               :USERSIGN,
                                               :EMAIL,
                                               :USERREMARK,
                                               :USERCITY,
                                               :CALLNO,
                                               :ACCEPTPHONE,
                                               :USERPHONE1,
                                               :USERPHONE2,
                                               :OWNER,
                                               :CONTACTNAME,
                                               :MANAGER,
                                               :USERMANAGERTEL,
                                               :NETTIME,
                                               :GREENSIGN,
                                               :FAXNO,
                                               :GROUPSERIAL,
                                               :GROUPMAINNAME,
                                               :GROUPMAINTEL,
                                               :SELLCENTER,
                                               :MAINSERVICEATTIUDE,
                                               :SERIALNO,
                                               :ACCEPTID,
                                               :ACCEPTTIME,
                                               :SERVICECITY,
                                               :SHEETAREA,
                                               :COMPLAINROUTE,
                                               :CONTATCID,
                                               :LANGUAGE,
                                               :HOPEREPLYTIME,
                                               :TROUBLEAPPEARANCE,
                                               :TERMINATYPE,
                                               :BLACKLISTFLAG,
                                               :ACCEPTERNAME,
                                               :ACCEPTERTEL,
                                               :DUFFTYPE,
                                               :ISUPGRADEAPPEAL,
                                               :CALLINCOUNT,
                                               :ISREPLY,
                                               :ISFACUSAPPEAL,
                                               :SPENTERNUM,
                                               :SPSERVICENAME,
                                               :SPFACTORY,
                                               :SPCORPNO,
                                               :DUTYASCERTAIN,
                                               :TYPEATTRIBUTE,
                                               :SPNO,
                                               :SPNAME,
                                               :SPPEACEBREAKER,
                                               :SPCONNECTMODE,
                                               :SPFORCECUSTOMIZE,
                                               :SPSTARTDATE,
                                               :SPENDDATE,
                                               :SPISALL,
                                               :WITHDRAWFEE,
                                               :REALWITHDRAWFEE,
                                               :COMPENSATETIMES,
                                               :COMPENSATETTAL,
                                               :SERVICECLASSID,
                                               :SERVICECONTENT,
                                               :SUMPOWERNUM,
                                               :AFFID,
                                               :ADDRESS,
                                               :STREETINFO,
                                               :BUILDINGINFO,
                                               :TEMPLATEID,
                                               :SERVICENAME,
                                               :PAYTYPE,
                                               :PAYMETHOD,
                                               :PAYMONEY,
                                               :PAYCLASS,
                                               :PAYREASON,
                                               :PAYACCEPTNO,
                                               :ISPAY,
                                               :ISACCEPT,
                                               :DUTYORGA,
                                               :OPERATIONPRODUCT,
                                               :OPTIONADDBYH1,
                                               :MUSTHANDLEDATE,
                                               :COMPLAINTS_ID,
                                               :CITY_ID,
                                               :SERIALINFONO,
                                               :ISREPEAT,
                                               :USERCOMPLAINTCITY,
                                               :CUSTOMERGRADE,
                                               :ISATTACHMENT)",
                                            SqlHelper.CreateParameter(":SHEETID", workOrderId.ToString()),
                                            SqlHelper.CreateParameter(":EXPLAIN", CheckedDbValue(sheetModel.Explain)),
                                            SqlHelper.CreateParameter(":USERTYPE", CheckedDbValue(sheetModel.Usertype)),
                                            SqlHelper.CreateParameter(":USERMEAL", CheckedDbValue(sheetModel.Usermeal)),
                                            SqlHelper.CreateParameter(":USERSIGN", CheckedDbValue(sheetModel.Usersign)),
                                            SqlHelper.CreateParameter(":EMAIL", CheckedDbValue(sheetModel.Email)),
                                            SqlHelper.CreateParameter(":USERREMARK", CheckedDbValue(sheetModel.Userremark)),
                                            SqlHelper.CreateParameter(":USERCITY", CheckedDbValue(sheetModel.Usercity)),
                                            SqlHelper.CreateParameter(":CALLNO", CheckedDbValue(sheetModel.Callno)),
                                            SqlHelper.CreateParameter(":ACCEPTPHONE", CheckedDbValue(sheetModel.Acceptphone)),
                                            SqlHelper.CreateParameter(":USERPHONE1", CheckedDbValue(sheetModel.Userphone1)),
                                            SqlHelper.CreateParameter(":USERPHONE2", CheckedDbValue(sheetModel.Userphone2)),
                                            SqlHelper.CreateParameter(":OWNER", CheckedDbValue(sheetModel.Owner)),
                                            SqlHelper.CreateParameter(":CONTACTNAME", CheckedDbValue(sheetModel.Contactname)),
                                            SqlHelper.CreateParameter(":MANAGER", CheckedDbValue(sheetModel.Manager)),
                                            SqlHelper.CreateParameter(":USERMANAGERTEL", CheckedDbValue(sheetModel.Usermanagertel)),
                                            SqlHelper.CreateParameter(":NETTIME", CheckedDbValue(sheetModel.Nettime)),
                                            SqlHelper.CreateParameter(":GREENSIGN", CheckedDbValue(sheetModel.Greensign)),
                                            SqlHelper.CreateParameter(":FAXNO", CheckedDbValue(sheetModel.Faxno)),
                                            SqlHelper.CreateParameter(":GROUPSERIAL", CheckedDbValue(sheetModel.Groupserial)),
                                            SqlHelper.CreateParameter(":GROUPMAINNAME", CheckedDbValue(sheetModel.Groupmainname)),
                                            SqlHelper.CreateParameter(":GROUPMAINTEL", CheckedDbValue(sheetModel.Groupmaintel)),
                                            SqlHelper.CreateParameter(":SELLCENTER", CheckedDbValue(sheetModel.Sellcenter)),
                                            SqlHelper.CreateParameter(":MAINSERVICEATTIUDE", CheckedDbValue(sheetModel.Mainserviceattiude)),
                                            SqlHelper.CreateParameter(":SERIALNO", CheckedDbValue(sheetModel.Serialno)),
                                            SqlHelper.CreateParameter(":ACCEPTID", CheckedDbValue(sheetModel.Acceptid)),
                                            SqlHelper.CreateParameter(":ACCEPTTIME", CheckedDbValue(sheetModel.Accepttime)),
                                            SqlHelper.CreateParameter(":SERVICECITY", CheckedDbValue(sheetModel.Servicecity)),
                                            SqlHelper.CreateParameter(":SHEETAREA", CheckedDbValue(sheetModel.Sheetarea)),
                                            SqlHelper.CreateParameter(":COMPLAINROUTE", CheckedDbValue(sheetModel.Complainroute)),
                                            SqlHelper.CreateParameter(":CONTATCID", CheckedDbValue(sheetModel.Contatcid)),
                                            SqlHelper.CreateParameter(":LANGUAGE", CheckedDbValue(sheetModel.Language)),
                                            SqlHelper.CreateParameter(":HOPEREPLYTIME", CheckedDbValue(sheetModel.Hopereplytime)),
                                            SqlHelper.CreateParameter(":TROUBLEAPPEARANCE", CheckedDbValue(sheetModel.Troubleappearance)),
                                            SqlHelper.CreateParameter(":TERMINATYPE", CheckedDbValue(sheetModel.Terminatype)),
                                            SqlHelper.CreateParameter(":BLACKLISTFLAG", CheckedDbValue(sheetModel.Blacklistflag)),
                                            SqlHelper.CreateParameter(":ACCEPTERNAME", CheckedDbValue(sheetModel.Acceptername)),
                                            SqlHelper.CreateParameter(":ACCEPTERTEL", CheckedDbValue(sheetModel.Acceptertel)),
                                            SqlHelper.CreateParameter(":DUFFTYPE", CheckedDbValue(sheetModel.Dufftype)),
                                            SqlHelper.CreateParameter(":ISUPGRADEAPPEAL", CheckedDbValue(sheetModel.Isupgradeappeal)),
                                            SqlHelper.CreateParameter(":CALLINCOUNT", CheckedDbValue(sheetModel.Callincount)),
                                            SqlHelper.CreateParameter(":ISREPLY", CheckedDbValue(sheetModel.Isreply)),
                                            SqlHelper.CreateParameter(":ISFACUSAPPEAL", CheckedDbValue(sheetModel.Isfacusappeal)),
                                            SqlHelper.CreateParameter(":SPENTERNUM", CheckedDbValue(sheetModel.Spenternum)),
                                            SqlHelper.CreateParameter(":SPSERVICENAME", CheckedDbValue(sheetModel.Spservicename)),
                                            SqlHelper.CreateParameter(":SPFACTORY", CheckedDbValue(sheetModel.Spfactory)),
                                            SqlHelper.CreateParameter(":SPCORPNO", CheckedDbValue(sheetModel.Spcorpno)),
                                            SqlHelper.CreateParameter(":DUTYASCERTAIN", CheckedDbValue(sheetModel.Dutyascertain)),
                                            SqlHelper.CreateParameter(":TYPEATTRIBUTE", CheckedDbValue(sheetModel.Typeattribute)),
                                            SqlHelper.CreateParameter(":SPNO", CheckedDbValue(sheetModel.Spno)),
                                            SqlHelper.CreateParameter(":SPNAME", CheckedDbValue(sheetModel.Spname)),
                                            SqlHelper.CreateParameter(":SPPEACEBREAKER", CheckedDbValue(sheetModel.Sppeacebreaker)),
                                            SqlHelper.CreateParameter(":SPCONNECTMODE", CheckedDbValue(sheetModel.Spconnectmode)),
                                            SqlHelper.CreateParameter(":SPFORCECUSTOMIZE", CheckedDbValue(sheetModel.Spforcecustomize)),
                                            SqlHelper.CreateParameter(":SPSTARTDATE", CheckedDbValue(sheetModel.Spstartdate)),
                                            SqlHelper.CreateParameter(":SPENDDATE", CheckedDbValue(sheetModel.Spenddate)),
                                            SqlHelper.CreateParameter(":SPISALL", CheckedDbValue(sheetModel.Spisall)),
                                            SqlHelper.CreateParameter(":WITHDRAWFEE", CheckedDbValue(sheetModel.Withdrawfee)),
                                            SqlHelper.CreateParameter(":REALWITHDRAWFEE", CheckedDbValue(sheetModel.Realwithdrawfee)),
                                            SqlHelper.CreateParameter(":COMPENSATETIMES", CheckedDbValue(sheetModel.Compensatetimes)),
                                            SqlHelper.CreateParameter(":COMPENSATETTAL", CheckedDbValue(sheetModel.Compensatettal)),
                                            SqlHelper.CreateParameter(":SERVICECLASSID", CheckedDbValue(sheetModel.Serviceclassid)),
                                            SqlHelper.CreateParameter(":SERVICECONTENT", CheckedDbValue(sheetModel.Servicecontent)),
                                            SqlHelper.CreateParameter(":SUMPOWERNUM", CheckedDbValue(sheetModel.Sumpowernum)),
                                            SqlHelper.CreateParameter(":AFFID", CheckedDbValue(sheetModel.Affid)),
                                            SqlHelper.CreateParameter(":ADDRESS", CheckedDbValue(sheetModel.Address)),
                                            SqlHelper.CreateParameter(":STREETINFO", CheckedDbValue(sheetModel.Streetinfo)),
                                            SqlHelper.CreateParameter(":BUILDINGINFO", CheckedDbValue(sheetModel.Buildinginfo)),
                                            SqlHelper.CreateParameter(":TEMPLATEID", CheckedDbValue(sheetModel.Templateid)),
                                            SqlHelper.CreateParameter(":SERVICENAME", CheckedDbValue(sheetModel.Servicename)),
                                            SqlHelper.CreateParameter(":PAYTYPE", CheckedDbValue(sheetModel.Paytype)),
                                            SqlHelper.CreateParameter(":PAYMETHOD", CheckedDbValue(sheetModel.Paymethod)),
                                            SqlHelper.CreateParameter(":PAYMONEY", CheckedDbValue(sheetModel.Paymoney)),
                                            SqlHelper.CreateParameter(":PAYCLASS", CheckedDbValue(sheetModel.Payclass)),
                                            SqlHelper.CreateParameter(":PAYREASON", CheckedDbValue(sheetModel.Payreason)),
                                            SqlHelper.CreateParameter(":PAYACCEPTNO", CheckedDbValue(sheetModel.Payacceptno)),
                                            SqlHelper.CreateParameter(":ISPAY", CheckedDbValue(sheetModel.Ispay)),
                                            SqlHelper.CreateParameter(":ISACCEPT", CheckedDbValue(sheetModel.Isaccept)),
                                            SqlHelper.CreateParameter(":DUTYORGA", CheckedDbValue(sheetModel.Dutyorga)),
                                            SqlHelper.CreateParameter(":OPERATIONPRODUCT", CheckedDbValue(sheetModel.Operationproduct)),
                                            SqlHelper.CreateParameter(":OPTIONADDBYH1", CheckedDbValue(sheetModel.Optionaddbyh1)),
                                            SqlHelper.CreateParameter(":MUSTHANDLEDATE", CheckedDbValue(sheetModel.Musthandledate)),
                                            SqlHelper.CreateParameter(":COMPLAINTS_ID", workOrderId.ToString()),
                                            SqlHelper.CreateParameter(":CITY_ID", cityid == -1 ? UserCityHelper.GetCityId(sheetModel.Usercity) : cityid/*UserCityHelper.GetCityId(workOrderDemo.Usercity)*/),
                                            SqlHelper.CreateParameter(":SERIALINFONO", CheckedDbValue(sheetModel.Serialinfono)),
                                            SqlHelper.CreateParameter(":ISREPEAT", (int)Convert.ToByte(isRepeat)),
                                            SqlHelper.CreateParameter(":USERCOMPLAINTCITY", CheckedDbValue(sheetModel.Usercomplaintcity)),
                                            SqlHelper.CreateParameter(":CUSTOMERGRADE", CheckedDbValue(sheetModel.Customergrade)),
                                            SqlHelper.CreateParameter(":ISATTACHMENT", CheckedDbValue(sheetModel.Isattachment)));

            return new NewSheetResponse(true, sheetModel.Serialno, workOrderId.ToString(), sheetModel.Serialinfono, @"工单 接收成功");
        }

        private object CheckedDbValue(object value)
        {
            switch (value.GetType().FullName)
            {
                case "System.String":
                    return string.IsNullOrEmpty(value.ToString()) ? DBNull.Value : value;
                default:
                    return DBNull.Value;
            }
        }

        protected void SaveToFile(string xmlString)
        {
            Log.Info("将工单保存为XML文件");
            DirectHelper.CreateDirectory(ConfigHelper.SaveToLocalPath);
            var xmlDoc = XmlHelper.ReadAsXmlDocument(xmlString);
            var workOrderDemo = new NewSheetModel(xmlDoc); //new NewSheetEntity(xmlDoc);
            xmlDoc.Save(string.Format(@"{0}\{1}.xml", ConfigHelper.SaveToLocalPath, workOrderDemo.Serialno));
        }

        protected virtual bool IsCheckOrder(NewSheetModel sheetModel)
        {
            if (sheetModel == null)
            {
                throw new NullReferenceException("未将对象引用实例");
            }

            // 9和10位数值是99为测试工单
            return sheetModel.Serialno[8] == '9' && sheetModel.Serialno[9] == '9';
        }

        public IResponse DoWork(string xmlString)
        {
            XmlDocument xmlDoc = null;
            try
            {
                // 联调不解析报文，直接返回错误
                if (ConfigurationManager.AppSettings["Debug"] == "1" && ConfigurationManager.AppSettings["UnDecodeXml"] == "1")
                {
                    return new NewSheetResponse(false, "-1", "-1", "-1", @"工单 无法解析文档");
                }
                xmlDoc = XmlHelper.ReadAsXmlDocument(xmlString);
            }
            catch (Exception exp)
            {
                Log.Debug(string.Format("工单 发送文字不符合XML格式，无法转换解析！\r\n{0}", xmlString));
                Log.Error(exp);
                return new NewSheetResponse(false, "-1", "-1", "-1", @"工单 发送文字不符合XML格式，无法转换解析！");
            }

            SaveToFile(xmlString);

            //NewSheetEntity entity;
            NewSheetModel entity;
            try
            {
                entity = new NewSheetModel(xmlDoc);// new NewSheetEntity(xmlDoc);
            }
            catch (Exception exp)
            {
                Log.Debug(string.Format("工单 无法解析文档\r\n{0}", xmlString));
                Log.Error(exp);
                return new NewSheetResponse(false, "-1", "-1", "-1", @"工单 无法解析文档");
            }

            IResponse reponse;
            // 测试工单
            if (IsCheckOrder(entity))
            {                
                try
                {
                    reponse = AddCheckOrder(entity);
                    // 发送回复
                    var send = new SendReplayToService();
                    string remark = string.Empty;
                    send.CallService(1, entity.Serialno, "已接收测试工单", "admin", "", ref remark);
                    Log.Info(remark);
                }
                catch (Exception ex)
                {
                    Log.Error(ex);
                    return new NewSheetResponse(false, entity.Serialno, "-1", entity.Serialinfono, @"工单 接收失败");
                    //return new NewSheetResponse(false, "-1", "-1", @"工单 接收失败");
                }
            }
            // 正常工单
            else
            {
                try
                {
                    // 如果是测试单则不入库保存本地
                    if (ConfigurationManager.AppSettings["Debug"] == "1")
                    {
                        reponse = new NewSheetResponse(true, entity.Serialno, Guid.NewGuid().ToString(), entity.Serialinfono, @"工单接收成功");
                    }
                    else
                    {
                        reponse = AddWorkOrder(entity);

                        try
                        {
                            if (reponse != null)
                            {
                                //自动派发工单
                                new NewSheetAutoModel().AutoSendTask(entity, reponse as NewSheetResponse);
                            }
                        }
                        catch (Exception ex)
                        {
                            Log.Error(ex);
                        }
                        // 工单合流  add by zhanghf 20120413
                        try
                        {
                            if (reponse != null)
                            {
                                //工单合流
                                new NewSheetAutoModel().StopTaskSetpState(reponse as NewSheetResponse);
                            }
                        }
                        catch (Exception ex)
                        {
                            Log.Error(ex);
                        }
                    }
                }
                catch (Exception ex)
                {
                    Log.Error(ex);
                    return new NewSheetResponse(false, entity.Serialno, "-1", entity.Serialinfono, @"工单 接收失败");
                    //return new NewSheetResponse(false, "-1", "-1", @"工单 接收失败");
                }
            }

            // 发送短信 add by heq 20120417
            try
            {
                new SmcServiceHelper<NewSheetModel>(entity).SendShortMessage();
            }
            catch (Exception ex)
            {
                Log.Error(ex);
            }

            return reponse;
        }
    }
}
